#!/bin/bash

# ----------------------------------------------------------------------
# Filename:   mysql_basefunction.sh
# Version:    1.0
# Date:       2013/11/27
# Author:     huijing.hei
# Email:      huijing.hei@cs2c.com.cn
# Summary:    02系统基本功能-04网络服务-03Mysql-03基本功能
# Notes:       ***
# Copyright:    China Standard Software Co., Ltd.
# History：     
#             Version 1.0, 2013/11/27
#             - Start mysqld service.
#             - Create database/table, and add/delete/update/select records.
# ----------------------------------------------------------------------

##! @TODO: start mysqld service
##! @AUTHOR: huijing.hei
##! @VERSION: 1.0 
##! @OUT: 0 => success; 1 => failure
function do_setup()
{
    trap do_cleanup INT EXIT

    serv=mysqld

CheckServiceExists ${serv}

if [ $? -ne 0 ];then
    echo "mysqld.service or mysqld not exists"
    CheckServiceExists mariadb
    EchoResult "mariadb.service or mariadb exists"
    serv=mariadb
fi


    RestartDaemon ${serv}

    # check start 
    StatusDaemon ${serv}
    EchoResult "service ${serv} start"

}

##! @TODO: test base functions of mysql
##! @AUTHOR: huijing.hei
##! @VERSION: 1.0 
##! @OUT: 0 => success; 1 => failure
function do_test()
{
    local TEST_USER=root
    local TEST_USER_PASSWORD=""
    local TEST_DATABASE=testdatabase
    local TEST_TABLE=testtable

    local INDEX=""
    if [ "${serv}" == "mysqld" ];then
        INDEX="mysql>"
    elif [ "${serv}" == "mariadb" ];then
        INDEX="MariaDB"
    fi

    expect <<-EOF
        set timeout 20
        spawn mysql -u${TEST_USER} -p
            expect "Enter password:"
            send "${TEST_USER_PASSWORD}\r"

            # login
            expect {
                "${INDEX}" { send "show databases;\r" }
                "ERROR 1045 (28000): Access denied" { send_user "Access denied\r"; exit 1; }
                "ERROR 2002 (HY000): Can't connect to local MySQL server through socket" { send_user "mysqld service not start, pls check\r"; exit 1; }
            }

            # check database mysql
            expect "mysql " { send_user "PASS: databases include mysql\n"  }
            expect "${INDEX}" { send "use mysql;\r" } 

            # 
            expect {
                "Database changed" { send_user "PASS: use mysql\n" }
                "ERROR 1049 (42000): Unknown database" { send_user "mysql database not exists, pls check\n"; exit 1; }
                "ERROR 2002 (HY000): Can't connect to local MySQL server through socket" { send_user "mysqld service not start, pls check\r"; exit 1; }
            }

            # show tables
            expect "${INDEX}" { send "show tables;\r" } 
            expect {
                "Tables_in_mysql" {  send_user "PASS: show tables\r" }
                "ERROR 2002 (HY000): Can't connect to local MySQL server through socket" { send_user "mysqld service not start, pls check\r"; exit 1; }
            }

            # describe table
            expect "${INDEX}" { send "describe user;\r" } 
            expect {
                "max_user_connections" {  send_user "PASS: describe user\r" }
                "ERROR 1146 (42S02):" { send_user "Table user doesn't exist\n"; exit 1; }
                "ERROR 2002 (HY000): Can't connect to local MySQL server through socket" { send_user "mysqld service not start, pls check\r"; exit 1; }
            }

            # select * from user
            expect "${INDEX}" { send "select * from user;\r" } 
            expect {
                "max_user_connections" {  send_user "PASS: select * from user\r" }
                "ERROR 1146 (42S02):" { send_user "Table user doesn't exist\n"; exit 1; }
                "ERROR 2002 (HY000): Can't connect to local MySQL server through socket" { send_user "mysqld service not start, pls check\r"; exit 1; }
            }


            ############
            # create database
            expect "${INDEX}" { send "create database ${TEST_DATABASE};\r" } 
            expect {
                "ERROR 1007 (HY000): Can't create database 'testdatabase'; database exists" { send "drop database ${TEST_DATABASE};\r"; exp_continue; }
                "Query OK" {  send_user "\nPASS\r" }
                "ERROR 2002 (HY000): Can't connect to local MySQL server through socket" { send_user "FAIL: mysqld service not start, pls check\r"; exit 1; }
            }

            # use database
            expect "${INDEX}" { send "use ${TEST_DATABASE};\r" } 
            expect {
                "Database changed" {  send_user "PASS: use ${TEST_DATABASE}\r" }
                "ERROR 1049 (42000): Unknown database" { send_user "database ${TEST_DATABASE} not exists, pls check\r"; exit 1; }
                "ERROR 2002 (HY000): Can't connect to local MySQL server through socket" { send_user "mysqld service not start, pls check\r"; exit 1; }
            }
            
            # create table
            expect "${INDEX}" { send "create table ${TEST_TABLE} (id int(3) auto_increment not null primary key, xm char(8),xb char(6),csny date);\r" } 
            expect {
                "Query OK" {  send_user "PASS: create table ${TEST_TABLE}\r" }
                "ERROR 2002 (HY000): Can't connect to local MySQL server through socket" { send_user "mysqld service not start, pls check\r"; exit 1; }
            }

            # describe table
            expect "${INDEX}" { send "describe ${TEST_TABLE};\r" } 
            expect {
                "csny" {  send_user "PASS: describe  ${TEST_TABLE}\r" }
                "ERROR 1146 (42S02):" { send_user "Table ${TEST_TABLE} doesn't exist\n"; exit 1; }
                "ERROR 2002 (HY000): Can't connect to local MySQL server through socket" { send_user "mysqld service not start, pls check\r"; exit 1; }
            }

            # insert records
            expect "${INDEX}" { send "insert into ${TEST_TABLE} values('','Lili','male','1981-11-01'); insert into ${TEST_TABLE} values('','Lucy','female','1982-05-20');\r" } 
            expect {
                "Query OK" {  send_user "PASS: insert into  ${TEST_TABLE}\r" }
                "ERROR 1146 (42S02):" { send_user "Table ${TEST_TABLE} doesn't exist\n"; exit 1; }
                "ERROR 2002 (HY000): Can't connect to local MySQL server through socket" { send_user "mysqld service not start, pls check\r"; exit 1; }
            }

            # select * records
            expect "${INDEX}" { send "select * from ${TEST_TABLE};\r" } 
            expect {
                "rows in set" {  send_user "PASS: select * from  ${TEST_TABLE}\r" }
                "ERROR 1146 (42S02):" { send_user "Table ${TEST_TABLE} doesn't exist\n"; exit 1; }
                "ERROR 2002 (HY000): Can't connect to local MySQL server through socket" { send_user "mysqld service not start, pls check\r"; exit 1; }
            }

            # update records
            expect "${INDEX}" { send "update ${TEST_TABLE} set csny='1971-01-10' where xm='Lili'; select * from ${TEST_TABLE} where xm='Lili';\r" } 
            expect {
                "Rows matched" {  send_user "PASS: update ${TEST_TABLE}\r" }
                "ERROR 1146 (42S02):" { send_user "Table ${TEST_TABLE} doesn't exist\n"; exit 1; }
                "ERROR 2002 (HY000): Can't connect to local MySQL server through socket" { send_user "mysqld service not start, pls check\r"; exit 1; }
            }

            # delete records
            expect "${INDEX}" { send "delete from ${TEST_TABLE} where xm='Lili';\r" } 
            expect {
                "Query OK" {  send_user "PASS: delete from  ${TEST_TABLE} where xm='Lili'\r" }
                "ERROR 1146 (42S02):" { send_user "Table ${TEST_TABLE} doesn't exist\n"; exit 1; }
                "ERROR 2002 (HY000): Can't connect to local MySQL server through socket" { send_user "mysqld service not start, pls check\r"; exit 1; }
            }

            # check
            # select * records
            expect "${INDEX}" { send "select * from ${TEST_TABLE} where xm='Lili';\r" } 
            expect {
                "Empty set" {  send_user "PASS: delete from ${TEST_TABLE} where xm='Lili'\r" }
                "ERROR 1146 (42S02):" { send_user "Table ${TEST_TABLE} doesn't exist\n"; exit 1; }
                "ERROR 2002 (HY000): Can't connect to local MySQL server through socket" { send_user "mysqld service not start, pls check\r"; exit 1; }
            }

            # drop table
            expect "${INDEX}" { send "drop table ${TEST_TABLE};\r" } 
            expect {
                "Query OK" {  send_user "PASS: drop table ${TEST_TABLE}\r" }
                "ERROR 1146 (42S02):" { send_user "Table ${TEST_TABLE} doesn't exist\n"; exit 1; }
                "ERROR 2002 (HY000): Can't connect to local MySQL server through socket" { send_user "mysqld service not start, pls check\r"; exit 1; }
            }

            # drop database
            expect "${INDEX}" { send "drop database ${TEST_DATABASE};\r" } 
            expect {
                "Query OK" {  send_user "PASS: drop database ${TEST_DATABASE}\r" }
                "ERROR 1008 (HY000):" { send_user "database ${TEST_DATABASE} doesn't exist\n"; exit 1; }
                "ERROR 2002 (HY000): Can't connect to local MySQL server through socket" { send_user "mysqld service not start, pls check\r"; exit 1; }
            }

            # quit
            expect "${INDEX}" { send "quit;\r" } 
EOF


}

##! @TODO: cleanup
##! @AUTHOR: huijing.hei
##! @VERSION: 1.0 
##! @OUT: 0 => success; 1 => failure
function do_cleanup()
{

    StopDaemon ${serv}
    EchoResult "stop ${serv}"

}


# include lib files

if [ -z "$SFROOT" ]
then
    echo "SFROOT is null, pls check"
    exit 1
fi

. ${SFROOT}/lib/Echo.sh
. ${SFROOT}/lib/NetOps.sh
. ${SFROOT}/lib/Check.sh

do_setup
do_test 
EchoResult "testcase: ${serv} base function"
echo ""
